import os.path
import time

from zn.common.component.component import TComponent , componentManager
from fastapi import APIRouter
from zn.common.router.root import app
from zn.common.utils.db_utils import TDBUtils

class TDB(TComponent):
    def __init__(self , _mananger):
        super().__init__(_mananger)
        self.pool=None

    def start(self):
        super().start()
        try:
            pass
        except Exception as er:
            self.manager.error(er)
    def init(self):
        super().init()
        try:
            pass
        except Exception as er:
            self.manager.error(er)
    def stop(self):
        super().stop()
        try:
            pass
        except Exception as er:
            self.manager.error(er)
    def getStatus(self):
        res = super().getStatus()
        try:
            res['status']={
                "time": time.time()
            }
        except Exception as er:
            self.manager.error(er)
        return res

    def query(self , sql , params):

        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if self.pool == None :
                settings = self.settings
                pool_status, pool = TDBUtils.getPool(
                    host=settings['host'],
                    port=settings['port'],
                    db=settings['db'],
                    uid=settings['uid'],
                    pwd=settings['pwd'],
                    dbType=settings['dbType'],
                )
                if pool!=None :
                    self.pool = pool
            if self.pool != None :
                dbType = self.settings['dbType']
                queryRes = TDBUtils.query(self.pool , sql , params , dbType)
            else:
                queryRes['error'] = {
                    "code":1000 ,
                    "msg":"dbCode is not exists"
                }
        except Exception as er:
            print(er)
        return queryRes
    def queryByKey(self , dataKey , params):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            settings = self.settings
            sqlPath = componentManager.getAbsPath(settings['sqlPath'])
            fn = (os.path.join(sqlPath, dataKey)).replace(" ", "")
            if fn[-4] != ".txt":
                fn = fn + ".txt"
            if os.path.exists(fn):
                sql = ""
                with open(fn, 'r', encoding='utf-8') as f:
                    sql = f.read()
                queryRes = self.query( sql, params)
            else:
                queryRes['error'] = {
                    "code": 1001,
                    "msg": f"dataKey [{dataKey}] is error."
                }
        except Exception as er:
            print(er)
        return queryRes
    def createRow(self,  table, fieldValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            fields = ','.join([x for x in fieldValues.keys()])
            values = ",".join(["%(" + x + ")s" for x in fieldValues.keys()])
            sql = "insert into " + table + "(" + fields + ") values (" + values + ")"
            queryRes = self.query( sql, fieldValues)
        except Exception as er:
            print(er)
        return queryRes
    def updateRow(self, table, fieldValues, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            fields = ','.join([x + '=%(' + x + ')s' for x in fieldValues.keys()])
            keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
            sql = "update " + table + ' set ' + fields + ' where ' + keys
            ps = {}
            for k in fieldValues.keys():
                ps[k] = fieldValues[k]
            for k in keyValues.keys():
                ps[k] = keyValues[k]
            queryRes = self.query( sql, ps)
        except Exception as er:
            print(er)
        return queryRes
    def createOrUpdateRow(self,  table, fieldValues, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            checkResult = self.viewRows( table, keyValues)
            if checkResult['queryStatus'] == 1:
                if len(checkResult['recordset']) == 0:
                    queryRes = self.createRow( table, fieldValues)
                else:
                    queryRes = self.updateRow( table, fieldValues, keyValues)
        except Exception as er:
            print(er)
        return queryRes
    def deleteRow(self,  dbCode , table, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if dbCode in self.settings.keys():
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                if keys =='' :
                    keys= '1>0'
                sql = "delete from  " + table + ' where ' + keys
                queryRes = self.query( dbCode, sql, keyValues)
            else :
                queryRes['error'] = {
                    "code":1000 ,
                    "msg":"dbCode is not exists"
                }
        except Exception as er:
            print(er)
        return queryRes
    def viewRows(self, view, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            sql = "select * from  " + view
            if keyValues!=None and len(keyValues.keys())>0:
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                sql = sql  + ' where ' + keys
            queryRes = self.query(sql, keyValues)
        except Exception as er:
            print(er)
        return queryRes
    def execProc(self, procName, keyValues={}):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            dbType = self.settings['dbType']
            if dbType == "mssql":
                ps = ",".join([f"%({x})s" for x in keyValues.keys()])
                sql = f"exec {procName} {ps}"
                queryRes = self.query( sql, keyValues)
            elif dbType == "mysql":
                ps = ",".join([f"%({x})s" for x in keyValues.keys()])
                sql = f"call {procName}({ps});"
                queryRes = self.query( sql, keyValues)
        except Exception as er:
            print(er)
        return queryRes



componentManager.registComponentType('TDB', TDB)